// =====================================================================
//  File:		StagingScripts.cs
//  Summary:	This class holds the properties & methods for staging database scripts
// =====================================================================
//  Copyright (c) 2008-2009 Avanade Inc.
//  All rights reserved.
// =====================================================================


using System;
using System.Collections.Generic;
using System.Text;

namespace Project.Co_DataTransformationPrototype.temp
{
    class StagingScripts
    {
        #region Constructor
        private StagingScripts()
        {

        }
        #endregion

        #region Variables
        static string INSERTINTOTABLE = "Insert into ";
        static string DELETEFROMTABLE = "Delete from ";
        static string UPDTAETABLE = "Update";
        #endregion

        #region GetScriptCommand
        /// <summary>
        /// This method provides the staging db script for relative arguments
        /// </summary>
        /// <param name="strCommand">action</param>
        /// <param name="arrParam">arguments</param>
        /// <returns>sql script</returns>
        public static string GetScriptCommand(string strCommand, string[] arrParam)
        {
            StringBuilder objString = new StringBuilder();
            string strTemp;

            switch (strCommand)
            {
                case "SELECT_ALL_ENTITIES_ATTRIBUTES":
                    objString.Append(" SELECT a.entityname, a.isactivity, b.attributename, b.sqldatatype, b.[datalength], b.CRMDatatype, b.LookupType  FROM [dms].ava_entity a left join [dms].ava_attribute b on a.entityname = b.entityname WHERE a.ENTITYNAME in ({0}) ORDER BY 1, 2 ");
                    break;
                case "UPDATE_GENERATED_ENTITIES_FALSE":
                    {
                        objString.Append(" UPDATE [dms].ava_Entity SET IsGenerated = 0");
                        break;
                    }
                case "UPDATE_GENERATED_ENTITIES_TRUE":
                    {
                        objString.Append(GetParametrizedSQL(" UPDATE [dms].ava_Entity SET IsGenerated = 1, IsModified = 0 WHERE EntityName IN ({1})", arrParam));
                        break;
                    }
                case "UPDATE_AVA_ENTITY_ROW":
                    {
                        strTemp = UPDTAETABLE + " [dms].ava_Entity SET ReferenceEntity = '{1}',InsertBatch={3},InsertSequence={4} Where EntityName = '{2}'; " + UPDTAETABLE + " [dms].ava_Entity SET InsertBatch = 1, InsertSequence = 10000 Where EntityName = '{2}' and InsertSequence is Null and InsertBatch is null and ReferenceEntity = 0";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                case "UPDATE_BATCH_STATUS":
                    {
                        strTemp = UPDTAETABLE + " [dms].ava_Entity SET UpdateBatch= {1},UpdateSequence = {2} Where EntityName = '{3}'";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                case "UPDATE_BATCH_STATUS_IF_UPDATE_NOT_EXIST":
                    {
                        strTemp = UPDTAETABLE + " [dms].ava_Entity SET UpdateBatch= {1},UpdateSequence = {2} Where EntityName = '{3}' AND UpdateBatch IS NULL  AND UpdateSequence is NULL ";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                case "UPDATE_IS_MODIFIED":
                    {
                        strTemp = UPDTAETABLE + " [dms].ava_Entity SET IsModified = {1} Where EntityName = '{2}'";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                case "UPDATE_AVA_ENTITY_RELATIONSHIP_ROW":
                    {
                        strTemp = UPDTAETABLE + " [dms].ava_Entity SET RelationshipName = '{1}' Where EntityName = '{2}'";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                case "SELECT_SELECTED_ENTITY_NON_INTERSECT":
                    {
                        objString.Append(" select EntityName,DisplayName,ReferenceEntity,Description,IsActivity from [dms].ava_Entity where IsIntersect=0 Order by EntityName ASC");
                        break;
                    }
                case "SELECT_METADATA_TABLE_NAMES":
                    {
                        objString.Append(GetParametrizedSQL(" select name from sys.tables where name in ('{1}','{2}')", arrParam));
                        break;
                    }
                case "SELECT_ENTITY_DETAILS":
                    {
                        objString.Append(GetParametrizedSQL(" select * from [dms].ava_ENTITY where EntityName = '{1}'", arrParam));
                        break;
                    }
                //case "SELECT_CRM_METADATA_DETAILS":
                //    {
                //        objString.Append(" select * from [dms].ava_CRMConnection");
                //        break;
                //    }
                case "SELECT_SELECTED_ENTITY_INTERSECT":
                    {
                        objString.Append(" select EntityName,DisplayName,ReferenceEntity,Description,IsActivity from [dms].ava_Entity Where IsIntersect=1 Order by EntityName ASC");
                        break;
                    }
                case "SELECT_SELECTED_ENTITY_ALL":
                    {
                        objString.Append(" select EntityName,DisplayName,ReferenceEntity,Description,IsActivity, IsGenerated, IsModified,IsIntersect  from [dms].ava_Entity Order by EntityName ASC");
                        break;
                    }
                case "SELECT_SELECTED_ATTRIBUTES_GIVEN_ENTITY":
                    {
                        objString.Append(GetParametrizedSQL(" select AttributeName as AttributeLogicalName,EntityName,DisplayName as AttributeDisplayName,SQLDatatype,CRMDatatype, DataLength as SQLDataLength,OperationType,FKSourceType,FKSourceType2,RelatedEntity as RelatedEntity1,RelatedAttribute as RelatedAttribute1,RelatedEntity2,RelatedAttribute2,ValidForCreateAPI as IsValidCreate,ValidForUpdateAPI as IsValidUpdate,IsRequire from [dms].ava_Attribute Where entityName = '{1}' Order by AttributeName ASC", arrParam));
                        break;
                    }
                case "SELECT_SELECTED_ATTRIBUTES_FOR_DEPENDENCY":
                    {
                        objString.Append(GetParametrizedSQL(" select AttributeName as AttributeLogicalName,EntityName,DisplayName as AttributeDisplayName,SQLDatatype,CRMDatatype, DataLength as SQLDataLength,OperationType,FKSourceType,FKSourceType2,RelatedEntity as RelatedEntity1,RelatedAttribute as RelatedAttribute1,RelatedEntity2,RelatedAttribute2,ValidForCreateAPI as IsValidCreate,ValidForUpdateAPI as IsValidUpdate,IsRequire from [dms].ava_Attribute Where entityName = '{1}' and (Lookuptype = 1 OR CRMDataType='Customer') and (FKsourceType in (1,2) OR FKsourceType2 in (1,2)) Order by AttributeName ASC", arrParam));
                        break;
                    }
                case "INSERT_AVA_ENTITY_ROW":
                    {
                        strTemp = INSERTINTOTABLE + " [dms].ava_Entity Values ('{1}','{2}','{3}','{4}','{5}',{6},{7},{8},{9},'{10}','{11}','{12}',{13});";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                case "INSERT_ATTRIBUTE_DETAILS":
                    {
                        strTemp = INSERTINTOTABLE + " [dms].ava_Attribute Values ('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}')";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                case "INSERT_PICKLIST_DETAILS":
                    {
                        strTemp = INSERTINTOTABLE + " [dms].ava_StringMap Values ('{1}','{2}','{3}','{4}','{5}')";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }

                //case "INSERT_AVA_CRM_SETTINGS":
                //    {
                //        strTemp = INSERTINTOTABLE + " [dms].ava_CRMConnection Values ('{1}','{2}','{3}','{4}','{5}','{6}', {7});";
                //        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                //        break;
                //    }
                case "DELETE_ENTITY":
                    {
                        strTemp = DELETEFROMTABLE + " [dms].ava_Entity Where EntityName = '{1}';IF OBJECT_ID('{1}') IS NOT NULL BEGIN drop table {1} END";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                case "DELETE_ATTRIBUTE_GIVEN_ENTITY":
                    {
                        strTemp = DELETEFROMTABLE + " [dms].ava_Attribute Where EntityName = '{1}';";
                        objString.Append(GetParametrizedSQL(strTemp, arrParam));
                        break;
                    }
                //case "DELETE_CRM_SETTINGS":
                //    { 
                //        objString.Append(DELETEFROMTABLE + " [dms].ava_CRMConnection");
                //        break;
                //    }
                case "DELETE_PICKLIST_DETAILS":
                    {
                        objString.Append(GetParametrizedSQL(DELETEFROMTABLE + " [dms].ava_StringMap Where EntityName='{1}'", arrParam));
                        break;
                    }
                case "CREATE_SCHEMA":
                    {
                        objString.Append(GetParametrizedSQL("Create Schema {1}", arrParam));
                        break;
                    }
                case "SELECT_SCHEMA_DETAILS":
                    {
                        objString.Append(GetParametrizedSQL("select * from sys.schemas Where [Name]='{1}'", arrParam));
                        break;
                    }
                case "DELETE_RECIPIENT_TABLE":
                    {
                        objString.Append(GetParametrizedSQL("IF OBJECT_ID('{1}Recipient') IS NOT NULL BEGIN drop table {1}Recipient END", arrParam));
                        break;
                    }
            }

            return objString.ToString();
        }
        #endregion

        #region GetParametrizedSQL
        /// <summary>
        /// Appends the sql arguments as parameter
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="arrParam"></param>
        /// <returns></returns>
        private static string GetParametrizedSQL(string strSQL, string[] arrParam)
        {
            if (arrParam != null && arrParam.Length > 0)
            {
                for (int count = 1; count <= arrParam.Length; count++)
                {
                    if (!string.IsNullOrEmpty(strSQL))
                    {
                        strSQL = strSQL.Replace("{" + count + "}", arrParam[count - 1]);
                    }
                }
            }
            return strSQL;
        }
        #endregion
    }
}
